package org.sxdata.jingwei.controller;

import net.sf.json.JSONObject;
import org.pentaho.di.core.KettleEnvironment;
import org.pentaho.di.core.database.DatabaseMeta;
import org.pentaho.di.core.exception.KettleXMLException;
import org.pentaho.di.core.plugins.PluginRegistry;
import org.pentaho.di.core.plugins.StepPluginType;
import org.pentaho.di.core.util.EnvUtil;
import org.pentaho.di.trans.Trans;
import org.pentaho.di.trans.TransHopMeta;
import org.pentaho.di.trans.TransMeta;
import org.pentaho.di.trans.step.StepMeta;
import org.pentaho.di.trans.steps.insertupdate.InsertUpdateMeta;
import org.pentaho.di.trans.steps.tableinput.TableInputMeta;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.*;
import java.util.*;

//作业调度
@Controller
@RequestMapping(value="/kettle")
public class KettleTranslate {

    protected static final Logger logger_info = LoggerFactory.getLogger(KettleTranslate.class);

    private static final String SQL = "SELECT * FROM ";// 数据库操作

    public static KettleTranslate kettleTranslate;

    //第一个的database需要有参数进行拼接，第二个的database默认为我本地的test数据库
    public static final String[] databasesXML = {
            "<?xml version=\"1.0\" encoding=\"UTF-8\"?>" +
                    "<connection>" +
                    "<name>bjdt</name>" +
                    "<server>127.0.0.1</server>" +
                    "<type>MYSQL</type>" +
                    "<access>Native</access>" +
                    "<database>kettle</database>" +
                    "<port>3306</port>" +
                    "<username>root</username>" +
                    "<password>123456</password>" +
                    "</connection>",
            "<?xml version=\"1.0\" encoding=\"UTF-8\"?>" +
                    "<connection>" +
                    "<name>kettle</name>" +
                    "<server>127.0.0.1</server>" +
                    "<type>MYSQL</type>" +
                    "<access>Native</access>" +
                    "<database>test</database>" +
                    "<port>3306</port>" +
                    "<username>root</username>" +
                    "<password>123456</password>" +
                    "</connection>"
    };

    public static void main(String args[]) {
        //声明Connection对象
        Connection con;
        //驱动程序名
        String driver = "com.mysql.jdbc.Driver";

        //URL指向要访问的数据库名mydata
        String url = "jdbc:mysql://localhost:3306/kettle";
        //MySQL配置时的用户名
        String user = "root";
        //MySQL配置时的密码
        String password = "123456";
        try {
            //加载驱动程序
            Class.forName(driver);
            //1.getConnection()方法，连接MySQL数据库！！
            con = DriverManager.getConnection(url, user, password);
            if (!con.isClosed())
                System.out.println("Succeeded connecting to the Database!");
            //2.创建statement类对象，用来执行SQL语句！！
            Statement statement = con.createStatement();

            //获取数据库的元数据
            DatabaseMetaData db = con.getMetaData();

            ResultSet rs = null;
            //从元数据中获取到所有的表名
            //rs = db.getTables(null, "%", "%", new String[]{"TABLE"});
            rs = db.getCatalogs(); //获取mysql所有数据库名
            while (rs.next()) {
                //System.out.println(rs.getString("TABLE_NAME"));  //输出指定数据库中的所有表
                System.out.println(rs.getString("TABLE_CAT"));  //输出mysql所有的数据库
            }

            Scanner sc = new Scanner(System.in);
            String str = sc.next(); //输入选择的表

            String createSql = "CREATE TABLE " + str + "(";

            rs = db.getPrimaryKeys(null,null,str);
            //System.out.println(rs.getString("COLUMN_NAME"));
            String prmkey = ""; //表主键
            if (!rs.isAfterLast()) {

                rs.next();

                System.out.println(rs.getString("TABLE_NAME") + " " +

                        rs.getString("COLUMN_NAME"));
                prmkey = rs.getString("COLUMN_NAME");
            }

            PreparedStatement pStemt = null;
            String tableSql = SQL + str;

            pStemt = con.prepareStatement(tableSql);;

            ResultSetMetaData rsmd = pStemt.getMetaData();

            int size = rsmd.getColumnCount();

            for(int i = 0; i < size; i++)
            {
                System.out.println("字段名为：" + rsmd.getColumnName(i + 1) + "; 字段类型为：" + rsmd.getColumnTypeName(i + 1) + "; 字段长度为；" + rsmd.getColumnDisplaySize(i+1));
                createSql = createSql + rsmd.getColumnName(i + 1) + " " + rsmd.getColumnTypeName(i + 1) + "(" + rsmd.getColumnDisplaySize(i+1) + "),";

            }
            createSql= createSql + "PRIMARY KEY (" + prmkey + ")";
            createSql = createSql + ") ENGINE=InnoDB DEFAULT CHARSET=utf8;";
            System.out.println(createSql);
            /*这一段代码是生成数据库表
            Connection trans = getConnection("test");
            Statement stat = trans.createStatement();
            stat.executeUpdate(createSql);
            stat.close();
            trans.close();*/

            //以下代码生成一个ktr文件
           /* KettleEnvironment.init();
            kettleTranslate = new KettleTranslate();
            TransMeta transMeta = kettleTranslate.generateMyOwnTrans();
            //String transXml = "<?xml version=\"1.0\" encoding=\"UTF-8\"?> \n" + transMeta.getXML();
            String transXml = transMeta.getXML();
            // System.out.println("transXml:"+transXml);
            String transName = "E:\\test\\first.ktr";
            File file = new File(transName);
            FileUtils.writeStringToFile(file, transXml, "UTF-8");*/

            //runKettleTransfer(null, "E:\\test\\first.ktr");
            rs.close();
            con.close();
        } catch (ClassNotFoundException e) {
            //数据库驱动类异常处理
            System.out.println("Sorry,can`t find the Driver!");
            e.printStackTrace();
        } catch (SQLException e) {
            //数据库连接失败异常处理
            e.printStackTrace();
        } catch (Exception e) {
            // TODO: handle exception
            e.printStackTrace();
        } finally {
            System.out.println("数据库数据成功获取！！");
        }
    }

    //获取所有数据源连接
    @RequestMapping(value="/getAllDatabases")
    @ResponseBody
    protected void getAllDatabases(HttpServletResponse response, HttpServletRequest request) throws IOException {
        //声明Connection对象
        try {
            Connection con = getConnection("test");
            //获取数据库的元数据
            DatabaseMetaData db = con.getMetaData();

            ResultSet rs = null;
            rs = db.getCatalogs();
            //List<String> listarr = new ArrayList<String>();

            JSONObject jsonObject = new JSONObject();
            int i = 0;
            while (rs.next()){
                jsonObject.put(i,rs.getString("TABLE_CAT"));
                i++;
            }
            //输出结果返回给客户端
            response.setContentType("text/html;charset=utf-8");
            PrintWriter out=response.getWriter();
            out.write(jsonObject.toString());
            out.flush();
            out.close();

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

    }

    //获取所有的Tables
    @RequestMapping(value="/getAllTables")
    @ResponseBody
    protected void getAllTables(HttpServletResponse response,HttpServletRequest request) throws IOException {
        //声明Connection对象
        try {
            String DatabaseName = request.getParameter("DatabaseName");
            Connection con = getConnection(DatabaseName);
            //获取数据库的元数据
            DatabaseMetaData db = con.getMetaData();

            ResultSet rs = null;

            rs = db.getTables(null, "%", "%", new String[]{"TABLE"});
            JSONObject jsonObject = new JSONObject();
            int i = 0;
            while (rs.next()) {
                jsonObject.put(i,rs.getString("TABLE_NAME"));
                i++;
            }
            //输出结果返回给客户端
            response.setContentType("text/html;charset=utf-8");
            PrintWriter out=response.getWriter();
            out.write(jsonObject.toString());
            out.flush();
            out.close();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }

    //获取数据库表中所有的字段名称
    @RequestMapping(value="/getTableAllCloumns")
    @ResponseBody
    protected void getTableAllCloumns(HttpServletResponse response,HttpServletRequest request) throws IOException {
        //声明Connection对象
        try {
            String DatabaseName = request.getParameter("DatabaseName");
            String TableName = request.getParameter("TableName");
            Connection con = getConnection(DatabaseName);
            //获取数据库的元数据

            PreparedStatement pStemt = null;
            String tableSql = SQL + TableName;

            pStemt = con.prepareStatement(tableSql);;

            ResultSetMetaData rsmd = pStemt.getMetaData();

            int size = rsmd.getColumnCount();
            JSONObject jsonObject = new JSONObject();
            for(int i = 0; i < size; i++)
            {
                jsonObject.put(i,rsmd.getColumnName(i + 1));
            }
            //输出结果返回给客户端
            response.setContentType("text/html;charset=utf-8");
            PrintWriter out=response.getWriter();
            out.write(jsonObject.toString());
            out.flush();
            out.close();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }

    //根据字段名称生成ktr文件并进行保存
    @RequestMapping(value="/saveKtr")
    @ResponseBody
    protected void saveKtr(HttpServletResponse response,HttpServletRequest request) throws IOException {
        //声明Connection对象
        try {
            String DatabaseName = request.getParameter("DatabaseName"); //数据库名称
            String TableName = request.getParameter("TableName"); //表名称
            String[] Columns = request.getParameterValues("Columns"); //字段数组
            Connection con = getConnection(DatabaseName);
            DatabaseMetaData db = con.getMetaData();
            ResultSet rs = null;
            rs = db.getPrimaryKeys(null,null,TableName);
            //System.out.println(rs.getString("COLUMN_NAME"));
            String prmkey = ""; //表主键
            if (!rs.isAfterLast()) {
                rs.next();
                prmkey = rs.getString("COLUMN_NAME");
            }

            PreparedStatement pStemt = null;
            String tableSql = SQL + TableName;

            pStemt = con.prepareStatement(tableSql);;

            ResultSetMetaData rsmd = pStemt.getMetaData();

            int size = rsmd.getColumnCount();
            JSONObject jsonObject = new JSONObject();
            for(int i = 1; i <= size; i++)
            {

            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }

    /**
     * 执行转换
     * @param initKettleParam
     * @param ktrFilePath  ktr文件所在路径
     * @return
     */
    public static boolean runKettleTransfer(Map<String, String> initKettleParam, String ktrFilePath) {
        Trans trans = null;
        String uuid = UUID.randomUUID().toString();
        logger_info.info("ExecKettleUtil@runKettleTransfer:" + uuid + " {ktrFilePath:" + ktrFilePath + "}");
        try {
            // 初始化
            KettleEnvironment.init();
            EnvUtil.environmentInit();
            TransMeta transMeta = new TransMeta(ktrFilePath);
            // 转换
            trans = new Trans(transMeta);
            // 初始化trans参数，脚本中获取参数值：${variableName}
            if (initKettleParam != null) {
                for (String variableName : initKettleParam.keySet()) {
                    trans.setVariable(variableName, initKettleParam.get(variableName));
                }
            }

            // 执行转换
            trans.execute(null);
            // 等待转换执行结束
            trans.waitUntilFinished();
            if (trans.getErrors() > 0) {
                logger_info.info("ExecKettleUtil@runKettleTransfer:" + uuid + " 执行失败");
                System.out.println("执行失败");
            } else {
                logger_info.info("ExecKettleUtil@runKettleTransfer:" + uuid + " 执行成功");
                System.out.println("执行成功");
            }
            return true;
        } catch (Exception e) {
            //logger_info.error("ExecKettleUtil@runKettleTransfer:" + uuid, e);
            return false;
        }
    }

    /**
     * 数据库连接
     * @param database 数据库名称
     * @return
     * @throws SQLException
     */
    public static Connection getConnection(String database) throws SQLException {
        Connection conn = null;
        try {
            String driver = "com.mysql.jdbc.Driver";
            //加载驱动程序
            try {
                Class.forName(driver);
                conn=DriverManager.getConnection("jdbc:mysql://localhost:3306/" + database, "root", "123456");
            } catch (ClassNotFoundException e) {
                e.printStackTrace();
            }
        } catch (SQLException e) {
            //LOGGER.error("获取数据库连接失败", e);
            e.printStackTrace();
        }
        return conn;
    }

    /**
     * 生成一个转化,把一个数据库中的数据转移到另一个数据库中,只有两个步骤,第一个是表输入,第二个是表插入与更新操作
     *
     * @return
     * @throws KettleXMLException
     */
    public TransMeta generateMyOwnTrans() throws KettleXMLException {
        System.out.println("************start to generate my own transformation***********");
        TransMeta transMeta = new TransMeta();
        // 设置转化的名称
        transMeta.setName("insert_update");
        // 添加转换的数据库连接
        for (int i = 0; i < databasesXML.length; i++) {
            DatabaseMeta databaseMeta = new DatabaseMeta(databasesXML[i]);
            transMeta.addDatabase(databaseMeta);
        }
        // registry是给每个步骤生成一个标识Id用
        PluginRegistry registry = PluginRegistry.getInstance();
        // 第一个表输入步骤(TableInputMeta)
        TableInputMeta tableInput = new TableInputMeta();
        String tableInputPluginId = registry.getPluginId(StepPluginType.class, tableInput);
        // 给表输入添加一个DatabaseMeta连接数据库
        DatabaseMeta database_bjdt = transMeta.findDatabase("bjdt");
        tableInput.setDatabaseMeta(database_bjdt);
        String select_sql = "SELECT * FROM r_user";  //这里的表名应该是参数
        tableInput.setSQL(select_sql);
        // 添加TableInputMeta到转换中
        StepMeta tableInputMetaStep = new StepMeta(tableInputPluginId, "table input", tableInput);
        // 给步骤添加在spoon工具中的显示位置
        tableInputMetaStep.setDraw(true);
        tableInputMetaStep.setLocation(100, 100);
        transMeta.addStep(tableInputMetaStep);
        // ******************************************************************
        // 第二个步骤插入与更新
        InsertUpdateMeta insertUpdateMeta = new InsertUpdateMeta();
        String insertUpdateMetaPluginId = registry.getPluginId(StepPluginType.class, insertUpdateMeta);
        // 添加数据库连接
        DatabaseMeta database_kettle = transMeta.findDatabase("kettle");
        insertUpdateMeta.setDatabaseMeta(database_kettle);
        // 设置操作的表
        insertUpdateMeta.setTableName("r_user");
        // 设置用来查询的关键字
        insertUpdateMeta.setKeyLookup(new String[] { "ID_USER" });  //一般是主键
        insertUpdateMeta.setKeyStream(new String[] { "ID_USER" });
        insertUpdateMeta.setKeyStream2(new String[] { "" });// 一定要加上
        insertUpdateMeta.setKeyCondition(new String[] { "=" });
        // 设置要更新的字段
        String[] updatelookup = { "ID_USER", "LOGIN", "PASSWORD" }; //也是参数，不过默认主键都是需要更新并且插入的
        String[] updateStream = { "ID_USER", "LOGIN", "PASSWORD" };
        Boolean[] updateOrNot = { true, true, true };
        insertUpdateMeta.setUpdateLookup(updatelookup);
        insertUpdateMeta.setUpdateStream(updateStream);
        insertUpdateMeta.setUpdate(updateOrNot);
        String[] lookup = insertUpdateMeta.getUpdateLookup();
        System.out.println("******:" + lookup[1]);
        // 添加步骤到转换中
        StepMeta insertUpdateStep = new StepMeta(insertUpdateMetaPluginId, "insert_update", insertUpdateMeta);
        insertUpdateStep.setDraw(true);
        insertUpdateStep.setLocation(250, 100);
        transMeta.addStep(insertUpdateStep);
        // ******************************************************************

        // ******************************************************************
        // 添加hop把两个步骤关联起来
        transMeta.addTransHop(new TransHopMeta(tableInputMetaStep, insertUpdateStep));
        System.out.println("***********the end************");
        return transMeta;
    }

    /*public static List<String> getTableNames() throws SQLException {
        List<String> tableNames = new ArrayList<>();
        Connection conn = getConnection();
        ResultSet rs = null;
        try {
            //获取数据库的元数据
            DatabaseMetaData db = conn.getMetaData();
            //从元数据中获取到所有的表名
            rs = db.getTables("lwj_odin_v2", null, null, new String[]{"TABLE"});
            while (rs.next()) {
                tableNames.add(rs.getString(3));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                rs.close();
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return tableNames;
    }*/
}
